package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import com.shsh.safemanage.model.Department1;
import com.shsh.safemanage.model.Department2;
import com.shsh.safemanage.model.Department3;
import com.shsh.safemanage.model.JobPost;
import com.shsh.safemanage.model.User;

public class DepartmentDao {
	public List<Department1> getDepartment1List(String nameLike, int departmentType) {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME " +
				"from department1 where DEPARTMENT1_NAME like '%" + nameLike + "%' and DEPARTMENT_TYPE=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
		List<Department1> list = new ArrayList<Department1>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, departmentType);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1 department1 = new Department1();
				department1.setId(rs.getLong("ID"));
				department1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				department1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));
				
				list.add(department1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return list;
	}
	
	public List<Department1> getDepartment1List(int departType) {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME from department1 where DEPARTMENT_TYPE=?";
		List<Department1> department1List = new ArrayList<Department1>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, departType);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1 depart1 = new Department1();
				depart1.setId(rs.getLong("ID"));
				depart1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				depart1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));
				
				department1List.add(depart1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return department1List;
	}
	
	public Department1 getDepartment1ByID(Long id) {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME from department1 where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department1 department1 = new Department1();
				department1.setId(id);
				department1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				department1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));
				
				return department1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public List<Department2> getDepartment2ListByDepartment1ID(Long department1ID) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_NAME from department2 where DEPARTMENT1_ID=?";
		List<Department2> department2List = new ArrayList<Department2>();
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, department1ID);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department2 depart2 = new Department2();
				depart2.setId(rs.getLong("ID"));
				depart2.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart2.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				
				department2List.add(depart2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return department2List;
	}
	
	public Department2 getDepartment2ByID(Long id) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_NAME from department2 where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department2 depart2 = new Department2();
				depart2.setId(rs.getLong("ID"));
				depart2.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart2.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				
				return depart2;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public List<Department3> getDepartment3ListByDepartment12ID(Long depart1ID, Long depart2ID) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_NAME " +
				"from department3 where DEPARTMENT1_ID=? and DEPARTMENT2_ID=?";
		List<Department3> department3List = new ArrayList<Department3>();
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			pstm.setLong(2, depart2ID);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department3 depart3 = new Department3();
				depart3.setId(rs.getLong("ID"));
				depart3.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart3.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				depart3.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				
				department3List.add(depart3);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return department3List;
	}
	
	public Department3 getDepartment3ByID(Long id) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_NAME from department3 where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department3 depart3 = new Department3();
				depart3.setId(rs.getLong("ID"));
				depart3.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart3.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				depart3.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				
				return depart3;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public List<JobPost> getJobPostListByDepartmentID(Long depart1ID, Long depart2ID) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_ID, POST_NAME, JOB_TITLE_ID from job_post " +
				"where DEPARTMENT1_ID=? and DEPARTMENT2_ID=?";
		List<JobPost> jobPostList = new ArrayList<JobPost>();
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			pstm.setLong(2, depart2ID);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				JobPost jobPost = new JobPost();
				jobPost.setId(rs.getLong("ID"));
				jobPost.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				jobPost.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				jobPost.setPostName(rs.getString("POST_NAME"));
				jobPost.setJobTitleID(rs.getInt("JOB_TITLE_ID"));
				
				jobPostList.add(jobPost);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return jobPostList;
	}
	
	public boolean addUserDepartment1Mapping(User registerUser, Set<Department1> department1Set) {
		String selectSql = "select ID from user_department1_mapping where EMPLOYEE_ID=? and CORRESPONDED_DEPARTMENT1_ID=?";
		String sql = "insert into user_department1_mapping(EMPLOYEE_ID, CORRESPONDED_DEPARTMENT1_ID) values(?, ?)";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm2 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSql);
			pstm2 = conn.prepareStatement(sql);
			for (Department1 depart1 : department1Set) {
				pstm.setLong(1, registerUser.getId());
				pstm.setLong(2, depart1.getId());
				
				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm2.setLong(1, registerUser.getId());
					pstm2.setLong(2, depart1.getId());
					
					pstm2.executeUpdate();
				}
			}
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
	
	public List<User> getUserIDsByDepartment2IDandRoleType(Long depart2ID, int roleTypes) {
		String sql = "select employee.ID, NAME from employee " +
				"left outer join job_post on employee.DEPARTMENT2_ID=job_post.DEPARTMENT2_ID " +
				"left outer join role on job_post.ROLE_ID=role.ID " +
				"where employee.DEPARTMENT2_ID=? and (role.ROLE_TYPE & ?) > 0";
		List<User> users = new ArrayList<User>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, depart2ID);
			pstm.setInt(2, roleTypes);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return users;
	}
	
	public List<User> getUserIDsByDepartment1IDandRoleType(Long depart1ID, int roleTypes) {
		String sql = "select distinct employee.ID, NAME from employee " +
				"left outer join job_post on employee.DEPARTMENT2_ID=job_post.DEPARTMENT2_ID " +
				"left outer join role on job_post.ROLE_ID=role.ID " +
				"where employee.DEPARTMENT1_ID=? and (role.ROLE_TYPE & ?) > 0";
		List<User> users = new ArrayList<User>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, depart1ID);
			pstm.setInt(2, roleTypes);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return users;
	}
}
